package cn.zlisp.demo_be;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.*;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/* JdbcTemplate方式的数据操作。
 */
@RestController
@RequestMapping("/staff")
public class StaffController {
  @Autowired // 自动连接DB
  JdbcTemplate jdbcTemplate;

  @RequestMapping("/demo")
  public Result getDemo() { // 构造一个总是成功的示例
    var staff = new Staff(0, "0", "Tom",
      LocalDate.of(2000, 12, 31),
      LocalDateTime.of(2021, 9, 30, 12, 34, 56),
      new BigDecimal("12345.67"));
    return new Result(0, "ok", staff, LocalDateTime.now(), "");
  }

  @PostMapping("/add")
  public Result addStaff(@RequestBody Staff s) {
    /* 增加1条记录。add/update的JSON请求参数就是Staff形式：
    {
      "staffId": 1,
      "staffCode": "2019007",
      "staffName": "Tom",
      "birthDate": "1970-12-31",
      "joinTime": "2000-09-30T12:34:56",
      "salary": 12345.67
    }
    */
    final String sql = """
    insert into staff(staff_id, staff_code, staff_name, birth_date, join_time, salary)
    values(?, ?, ?, ?, ?, ?)
    """; // 多行字符串需要新版JDK。
    int n = jdbcTemplate.update(sql, s.staffId(), s.staffCode(), s.staffName(),
      s.birthDate(), s.joinTime(), s.salary());
    return new Result(0, "ok", null, LocalDateTime.now(),
      String.format("Insert %d row(s).", n));
  }

  // Restful推荐用DELETE请求。但是换请求类型太麻烦，不如用路径区分。
  @PostMapping("/remove/{id}")
  public Result removeStaff(@PathVariable("id") int staff_id) {
    // 删除1条记录。
    final String sql = "delete from staff where staff_id = ?";
    int n = jdbcTemplate.update(sql, staff_id);
    return new Result(0, "ok", null, LocalDateTime.now(),
      String.format("Remove %d row(s).", n));
  }

  @PostMapping("/update")
  public Result updateStaff(@RequestBody Staff s) {
    // 更新是非常复杂的动作，应当考虑业务仔细拆分成更细粒度的接口，不应这样简单允许更新所有字段。
    final String sql = """
    update  staff s
    set     staff_code = ?, staff_name = ?, birth_date = ?, join_time = ?, salary = ?
    where   staff_id = ?
    """;
    int n = jdbcTemplate.update(sql, s.staffCode(), s.staffName(), s.birthDate(),
      s.joinTime(), s.salary(), s.staffId());
    return new Result(0, "ok", null, LocalDateTime.now(),
      String.format("Update %d row(s).", n));
  }

  @PostMapping("/get/{id}")
  public Result getStaff(@PathVariable("id") int staff_id) {
    // 列举记录。可以加查询条件、分页等。
    final String sql = """
    select  staff_id, staff_code, staff_name, birth_date, join_time, salary
    from    staff t
    where   t.staff_id = ?
    order   by t.staff_id
    """;
    Staff staff = jdbcTemplate.queryForObject(sql, new RowMapper<Staff>(){
      @Override
      public Staff mapRow(ResultSet rs, int i) throws SQLException {
        return new Staff(
          rs.getInt("staff_id"),
          rs.getString("staff_code"),
          rs.getString("staff_name"),
          rs.getDate("birth_date").toLocalDate(),
          rs.getTimestamp("join_time").toLocalDateTime(),
          rs.getBigDecimal("salary")
        );
      }
    }, staff_id);
    return new Result(0,"ok", staff, LocalDateTime.now(), "");
  }

  @PostMapping("/list")
  public Result listStaff() {
    // 列举记录。可以加查询条件、分页等。
    final String sql = """
    select  staff_id, staff_code, staff_name, birth_date, join_time, salary
    from    staff t
    order   by t.staff_id
    """;
    List<Staff> lst = jdbcTemplate.query(sql, new RowMapper<Staff>(){
      @Override
      public Staff mapRow(ResultSet rs, int i) throws SQLException {
        return new Staff(
          rs.getInt("staff_id"),
          rs.getString("staff_code"),
          rs.getString("staff_name"),
          rs.getDate("birth_date").toLocalDate(),
          rs.getTimestamp("join_time").toLocalDateTime(),
          rs.getBigDecimal("salary")
        );
      }
    });
    return new Result(0,"ok", lst, LocalDateTime.now(), "");
  }
}
